﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using CBS.Model.ManageModel;
using CBS.Model.ViewModel;
using CBS.Model.UIModel;
using MySqlConnector;
using System.Data.Common;
using Org.BouncyCastle.Crypto;
using DocumentFormat.OpenXml.Office2010.Excel;

namespace CBS.APP
{
    public class StudentApp
    {
        public static bool CreateStudent(StudentViewModel student, out string msg)
        {
            msg = "";
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                cbs_student stu = new cbs_student
                {
                    StudentName = student.StudentName,
                    PhoneNumber = student.PhoneNumber,
                    WxCode = student.WxCode,
                };

                cbs.cbs_student.Add(stu);
                cbs.SaveChanges();

                cbs.cbs_student_amount.Add(new cbs_student_amount
                {
                    StudentId = stu.StudentId,
                    Balance = student.Balance,
                });

                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                transaction.Rollback();
                throw;
            }
        }

        public static bool UpdateStudentInfo(StudentViewModel student)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var stu = cbs.cbs_student.FirstOrDefault(t => t.StudentId == student.StudentId);
                if (stu == null) { transaction.Rollback(); return false; }


                stu.PhoneNumber = student.PhoneNumber;
                stu.WxCode = student.WxCode;
                cbs.cbs_student.Update(stu);
                cbs.SaveChanges();

                var amount = cbs.cbs_student_amount.FirstOrDefault(t => t.StudentId == student.StudentId);
                if (amount == null) { transaction.Rollback(); return false; }

                amount.Balance = student.Balance;
                cbs.cbs_student_amount.Update(amount);
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw;
            }
        }


        public static List<StudentViewModel> GetList(string keyword, Pagination page)
        {
            string sql = "select a.*,b.Balance\r\nfrom cbs_student a\r\n\tleft join cbs_student_amount b on a.StudentId = b.StudentId\r\nwhere @keyword = '' or a.StudentName like @keyword";
            MySqlParameter[] para =
            {
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<StudentViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static List<StudentSelectModel> GetSelectList()
        {
            using CBSContext cbs = new();
            var list = (from s in cbs.cbs_student
                        select new StudentSelectModel
                        {
                            StudentId = s.StudentId,
                            StudentName = s.StudentName
                        }).ToList();

            return list;
        }

        public static bool DeleteStudent(int StudentId, out string msg)
        {
            msg = "";
            using CBSContext cbs = new();
            if (cbs.cbs_student_signup.Any(t => t.StudentId == StudentId)
                || cbs.cbs_student_sign_detail.Any(t => t.StudentId == StudentId)
                || cbs.cbs_student_bonus.Any(t => t.IntroduceStudentId == StudentId))
            {
                msg = "学生已经报到，签到或者是介绍人，不能删除。";
                return false;
            }
            cbs.cbs_student.Remove(cbs.cbs_student.First(t => t.StudentId == StudentId));

            return cbs.SaveChanges() > 0;
        }

        public static bool BatchDeleteStudent(string key, out string msg)
        {
            msg = "";
            using CBSContext cbs = new();
            var data = cbs.cbs_student.Where(t => t.StudentName.Contains(key));
            var ids = data.Select(t => t.StudentId).ToArray();
            if (cbs.cbs_student_signup.Any(t => ids.Contains(t.StudentId))
                || cbs.cbs_student_sign_detail.Any(t => ids.Contains(t.StudentId))
                || cbs.cbs_student_bonus.Any(t => ids.Contains(t.IntroduceStudentId)))
            {
                msg = "学生已经报到，签到或者是介绍人，不能删除。";
                return false;
            }
            cbs.cbs_student.RemoveRange(data);
            return cbs.SaveChanges() > 0;
        }

        public static StudentViewModel GetForm(int StudentId)
        {
            using CBSContext cbs = new();
            var entity = cbs.cbs_student.FirstOrDefault(t => t.StudentId == StudentId);
            var amount = cbs.cbs_student_amount.FirstOrDefault(t => t.StudentId == StudentId);
            StudentViewModel viewModel = new StudentViewModel
            {
                StudentId = StudentId,
                StudentName = entity.StudentName,
                Balance = amount.Balance,
                PhoneNumber = entity.PhoneNumber,
                WxCode = entity.WxCode,
            };
            return viewModel;
        }

    }
}
